﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Windows.Forms;
using System.Data.OleDb;
using doanqlhs.DESIGN;
using doanqlhs.DATA;
using System.Data;

namespace doanqlhs
{
    public class BUS
    {
        #region HàmThaoTácCSDL
        
        /*  NhapSV: Kiểm tra việc nhập vào bảng Table --> thêm vào CSDL
        *  Trả về 0 nếu thỏa mọi quy định, -1 nếu thiếu thông tin, -2 nếu bị trùng
        */

        public static int Nhap(string Table, List<string> _in_component)
        {
            #region Kiểm_tra_điều_kiện
            switch (Table)
            {
                case "SV":
                    {

                        foreach (string x in _in_component)
                        {
                            if (x == "")
                                return -1;
                        }

                    }
                    break;
                case "KHOILOP":
                    {
                        foreach (string x in _in_component)
                        {
                            if (x == "")
                                return -1;
                        }
                    }
                    break;
                case "LOP":
                    {
                        foreach (string x in _in_component)
                        {
                            if (x == "")
                                return -1;
                        }
                    }
                    break;

                case "MONHOC":
                    {
                        foreach (string x in _in_component)
                        {
                            if (x == "")
                                return -1;
                        }
                    }
                    break;
                case "BANGDIEM":
                    {
                        foreach (string x in _in_component)
                        {
                            if (x == "")
                                return -1;
                        }
                    }
                    break;
                case "THAMSO":
                    {
                        foreach (string x in _in_component)
                        {
                            if (x == "")
                                return -1;
                        }
                    }
                    break;
                default:
                    return -1;
            }
            #endregion

            #region ThêmVàoCSDL
            int kq = OleDB.Insert(Table, _in_component);
            if (kq != 0)
            {
                return -2;
            }
            #endregion
            return 0;
        }

        /*  ThayDoi: Kiểm tra việc nhập vào bảng Table --> sua doi CSDL tại bộ có khóa chính = key 
       *  Trả về 0 nếu thỏa mọi quy định, -1 nếu thiếu thông tin, -2 nếu bị trùng
       */

        public static int ThayDoi(string Table, List<string> _in_component)
        {
            #region Kiểm_tra_điều_kiện
            switch (Table)
            {
                case "LOP":
                    {
                        foreach (string x in _in_component)
                        {
                            if (x == "")
                                return -1;
                        }
                    }
                    break;

                case "MONHOC":
                    {
                        foreach (string x in _in_component)
                        {
                            if (x == "")
                                return -1;
                        }
                    }
                    break;
                case "THAMSO":
                    {
                        foreach (string x in _in_component)
                        {
                            if (x == "")
                                return -1;
                        }
                    }
                    break;
                default:
                    return -1;
            }
            #endregion

            #region ThêmVàoCSDL
            int kq = OleDB.Update(Table, _in_component);
            if (kq != 0)
            {
                return -2;
            }
            #endregion
            return 0;
        }
        #endregion
        #region PhầnXửLýChoFormLậpDSlớp
        /*  
         *  LaySiso: 
         */

        public static void DienSiSo(string _in, System.Windows.Forms.TextBox _inText)
        {
            OleDB.g_Sql = "select * from [LOP] where MALOP = '" + _in +"'";
            OleDB.g_command = new OleDbCommand(OleDB.g_Sql, OleDB.g_connection);
            OleDB.g_reader = OleDB.g_command.ExecuteReader();
            while (OleDB.g_reader.Read())
            {
                string strContent = "";
                strContent = strContent + OleDB.g_reader.GetInt32(3).ToString();
                _inText.Text = strContent;
               
            }
        }
        /*  
         *  Dien Thong tin Hoc Sinh: 
         */

        public static void DienThongTinSV(string _inMaLop, System.Windows.Forms.DataGridView _inGird)
        {
            OleDB.g_Sql = "select MASV,HOTEN,NGAYSINH,GIOITINH,DIACHI from [SV] where MALOP = '" + _inMaLop + "'";
            OleDB.g_command = new OleDbCommand(OleDB.g_Sql, OleDB.g_connection);
            OleDbDataAdapter oledbAdapter = new OleDbDataAdapter(OleDB.g_Sql, OleDB.g_connection);
            //create a command builder
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(oledbAdapter);
            //create a DataTable to hold the query results
            DataTable dTable = new DataTable();
            //fill the DataTable
            oledbAdapter.Fill(dTable);
            //BindingSource to sync DataTable and DataGridView
            BindingSource bSource = new BindingSource();
            //set the BindingSource DataSource
            bSource.DataSource = dTable;
            _inGird.DataSource = bSource;

        }
        #endregion
        #region PhầnXửLýChoFormBangDiemMon
        public static void DienBangDiemMon(List<string> _inInfo, System.Windows.Forms.DataGridView _inGird)
        {
            OleDB.g_Sql = "select [SV].HOTEN, [BANGDIEM].DIEM15,[BANGDIEM].DIEM1T,[BANGDIEM].DIEMCUOIHK" 
                    + " from [BANGDIEM],[SV],[MONHOC]"
                    + " where [BANGDIEM].MASV = [SV].MASV and [BANGDIEM].MAMH = [MONHOC].MAMH and [BANGDIEM].MAHK = '" + _inInfo[2] + "' and " + "[MONHOC].MONHOC = '" + _inInfo[1] + "' and [SV].MALOP = '" + _inInfo[0] + "'";
            OleDB.g_command = new OleDbCommand(OleDB.g_Sql, OleDB.g_connection);
            OleDbDataAdapter oledbAdapter = new OleDbDataAdapter(OleDB.g_Sql, OleDB.g_connection);
            //create a command builder
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(oledbAdapter);
            //create a DataTable to hold the query results
            DataTable dTable = new DataTable();
            //fill the DataTable
            oledbAdapter.Fill(dTable);
            //BindingSource to sync DataTable and DataGridView
            BindingSource bSource = new BindingSource();
            //set the BindingSource DataSource
            bSource.DataSource = dTable;
            _inGird.DataSource = bSource;

        }
        #endregion
        #region PhầnXửLýChoFormDSHS
        public static void DienDanhSachHocSinh(System.Windows.Forms.DataGridView _inGird)
        {
            OleDB.g_Sql = "select master.MASV,master.MAHK,AVG(master.DTB)"
                           + "from (select [BANGDIEM].MASV as MASV,[BANGDIEM].MAHK as MAHK, (DIEM15+DIEM1T+DIEMCUOIHK)/3 as DTB"
                                    + " from [BANGDIEM]) as master"
                                    + " group by master.MASV,master.MAHK";
            OleDB.g_command = new OleDbCommand(OleDB.g_Sql, OleDB.g_connection);
            OleDbDataAdapter oledbAdapter = new OleDbDataAdapter(OleDB.g_Sql, OleDB.g_connection);
            //create a command builder
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(oledbAdapter);
            //create a DataTable to hold the query results
            DataTable dTable = new DataTable();
            //fill the DataTable
            oledbAdapter.Fill(dTable);
            //BindingSource to sync DataTable and DataGridView
            BindingSource bSource = new BindingSource();
            //set the BindingSource DataSource
            bSource.DataSource = dTable;
            _inGird.DataSource = bSource;

        }
        #endregion
        #region PhầnXửLýCácformkhác

        /*  
         *  DataToComboBox: Lấy dữ liệu từ cột Column trong bảng Table của Data điền vào comboBox
         */

        public static void DataToComboBox(string Table, int Column, System.Windows.Forms.ComboBox _in_cb)
        {
            OleDB.g_Sql = "select * from [" + Table + "]";
            OleDB.g_command = new OleDbCommand(OleDB.g_Sql, OleDB.g_connection);
            OleDB.g_reader = OleDB.g_command.ExecuteReader();
            while (OleDB.g_reader.Read())
            {
                string strContent = "";
                strContent = strContent + OleDB.g_reader.GetString(Column);
                _in_cb.Items.Add(strContent);
            }
        }
        /*  
         *  IsExist: Kiểm tra chuỗi string có tồn tại trong cột Column của bảng Table 
         *  Trả về 0 nếu có, -1 nếu không
         */
        public static int IsExist(string Table, int Column, string _in_ChuoiCanTim)
        {
            if (_in_ChuoiCanTim != "")
            {
                OleDB.g_Sql = "select * from [" + Table +"]";
                OleDB.g_command = new OleDbCommand(OleDB.g_Sql, OleDB.g_connection);
                OleDB.g_reader = OleDB.g_command.ExecuteReader();
                while (OleDB.g_reader.Read())
                {
                    string strContent = "";
                    strContent = strContent + OleDB.g_reader.GetString(Column);
                    if (strContent == _in_ChuoiCanTim)
                        return 0;
                }
            }
            return -1;
        }
        #endregion

    }
}
